import sqlalchemy
import generate_data
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.exc import SQLAlchemyError
from datetime import datetime, date
import json
import cx_Oracle


def new_engine_by_db_type(db_type):
    if db_type == 'mysql':
        engine = create_engine('mysql+pymysql://root:123456@localhost:3306/db01')
        return engine
    elif db_type == 'oracle':
        username = 'your_username'
        password = 'your_password'
        host = 'your_host'
        port = 'your_port'
        service_name = 'your_service_name'

        # 构建连接字符串
        # 格式：oracle+cx_oracle://<username>:<password>@<host>:<port>/<service_name>
        engine = create_engine(
            f'oracle+cx_oracle://{username}:{password}@{host}:{port}/?service_name={service_name}'
        )
        cx_Oracle.init_oracle_client(config_dir="/opt/oracle/your_config_dir")
        return engine
    elif db_type == 'sql_server':
        # 数据库连接信息
        server = 'your_server_name'
        database = 'your_database_name'
        username = 'your_username'
        password = 'your_password'

        # 构建连接字符串
        connection_string = f'mssql+pymssql://{username}:{password}@{server}/{database}'

        # 创建数据库引擎
        engine = create_engine(connection_string)
        return engine
    elif db_type == 'postgresql':
        # 数据库连接信息
        user = 'your_username'
        password = 'your_password'
        host = 'your_host'
        port = 'your_port'
        database = 'your_database'

        # 构建连接字符串
        # 格式：postgresql://<user>:<password>@<host>:<port>/<database>
        connection_string = f'postgresql://{user}:{password}@{host}:{port}/{database}'

        # 创建数据库引擎
        engine = create_engine(connection_string)

    else:
        raise ValueError("该数据库类型不支持")


def insert_data(engine, table_name, data):
    """
    向指定数据库表插入数据，自动适配表结构。

    参数：
        db_url (str): SQLAlchemy格式的数据库连接URL。
        table_name (str): 目标表名。
        data (dict): 要插入的数据，键为字段名，值为数据值（支持基本类型或日期字符串）。
    """

    metadata = MetaData()

    try:
        # 反射获取表结构
        table = Table(table_name, metadata, autoload_with=engine)

        # 预处理数据，转换日期字符串
        processed_data = {}
        for column_name, value in data.items():
            if column_name not in table.columns:
                raise ValueError(f"列 '{column_name}' 不存在于表 {table_name} 中")

            # 获取列的数据类型
            col_type = table.columns[column_name].type.__visit_name__

            # 处理日期和时间类型
            if isinstance(value, str):
                if col_type in ('date', 'datetime'):
                    try:
                        # 尝试解析日期字符串
                        if col_type == 'date':
                            processed_value = datetime.strptime(value, '%Y-%m-%d').date()
                        else:
                            processed_value = datetime.strptime(value, '%Y-%m-%d %H:%M:%S')
                    except ValueError:
                        raise ValueError(f"列 '{column_name}' 的值 '{value}' 无法转换为 {col_type}")
                    processed_data[column_name] = processed_value
                else:
                    processed_data[column_name] = value
            else:
                processed_data[column_name] = value

        # 构建并执行插入语句
        with engine.connect() as conn:
            stmt = table.insert().values(**processed_data)
            conn.execute(stmt)
            conn.commit()
        print("数据插入成功！")

    except SQLAlchemyError as e:
        print(f"数据库操作错误: {str(e)}")
        raise
    except Exception as e:
        print(f"错误: {str(e)}")
        raise
